package com.kfm.jdbc.day01;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库工具类
 * <p>
 * 提供获取连接的方法
 * CRUD 方法
 */
public class DBHelper {

    // 数据库连接信息
    private String url;

    private String user;

    private String pass;

    // 操作对象
    private Connection conn;

    private PreparedStatement ps;

    private ResultSet rs;

    static{
        try {
            // 加载驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public DBHelper(String url, String user, String pass) {
        this.url = url;
        this.user = user;
        this.pass = pass;
    }

    /**
     * 获取数据库连接
     * @return
     */
    public Connection getConnection(){
        if (conn == null){
            try {
                conn = DriverManager.getConnection(url, user, pass);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return conn;
    }

    /**
     * 释放资源
     */
    public void close(){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            rs = null;
        }
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            ps = null;
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            conn = null;
        }
    }

    /**
     * 修改数据操作
     * insert  update delete
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */

    public int update(String sql, Object... params) throws SQLException {
        // 获取连接
        getConnection();
        // 创建 PreparedStatement 对象
        ps = conn.prepareStatement(sql);

        // 设置值
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }

        // 执行 sql
        return ps.executeUpdate();
    }

    /**
     * 新增并返回自增长值
     * @return
     */
    public int insert(String sql, Object... params) throws SQLException {
        // 获取连接
        getConnection();

        // 创建 prepareStatement
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        // 设置值
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }

        // 执行操作
        ps.executeUpdate();


        // 去自增长值
        rs = ps.getGeneratedKeys();

        // 取值
        if (rs.next()){
            return rs.getInt(1);
        }
        return -1;
    }


    public <T> T selectOne(String sql, IResultMapper<T> resultMapper,  Object... params) throws SQLException {
        // 获取连接
        getConnection();

        // 创建 prepareStatement
        ps = conn.prepareStatement(sql);

        // 设置值
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }

        // 执行操作
        rs = ps.executeQuery();

        // 解析结果
        if (rs.next()){
            return resultMapper.mapper(rs);
        }
        return null;
    }

    public <T> List<T> selectList(String sql, IResultMapper<T> resultMapper,  Object... params) throws SQLException {
        // 获取连接
        getConnection();

        // 创建 prepareStatement
        ps = conn.prepareStatement(sql);

        // 设置值
        if (params != null){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }

        // 执行操作
        rs = ps.executeQuery();

        List<T> list = new ArrayList<>();
        // 解析结果
        while (rs.next()){
            T t = resultMapper.mapper(rs);
            list.add(t);
        }

        return list;
    }
}

/**
 * 结果映射
 */
@FunctionalInterface
interface IResultMapper<T> {

    T mapper(ResultSet set);
}

class EmpResultMapper implements IResultMapper<Emp> {

    @Override
    public Emp mapper(ResultSet set) {
        Emp emp = new Emp();
        try {
            emp.setEmpno(set.getInt("empno"));
            emp.setEname(set.getString("ename"));
            emp.setJob(set.getString("job"));
            emp.setHiredate(set.getDate("hiredate"));
            emp.setMgr(set.getInt("mgr"));
            emp.setSal(set.getDouble("sal"));
            emp.setComm(set.getDouble("comm"));
            emp.setDeptno(set.getInt("deptno"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return emp;
    }
}
